﻿CREATE PROCEDURE [dbo].[proc_Statistics_Financial_Tongji_G_ZHICHU]
	(
		@CompanyId VARCHAR(500),
		@StartDate nchar(10),
		@EndDate nchar(10)
	)
AS
DECLARE @strSql VARCHAR(max),@strSql1 VARCHAR(max),@strSql2 VARCHAR(max),@strSql3 VARCHAR(max),@strSql4 VARCHAR(max)

set @strSql2=''
if @CompanyId<>'0'
begin
	set @strSql2=' where Id in ('+@CompanyId+') '
end

	SET @strSql='
	
	Declare @sRq Datetime,@eRq Datetime,@eRq_s Datetime
	Set @sRq = Convert(Datetime, '''+@StartDate + ' 00:00:00'')
	Set @eRq = Convert(Datetime, '''+@EndDate + ' 23:59:59'')
	Set @eRq_s = Convert(Datetime, '''+@EndDate + ' 00:00:00'')
	
	
	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY (Select M.CompanyNumber From ManagementArea M Where M.Id=Mid) ASC,dwbh)AS Row
	,c.Id As CompanyId
	,c.CompanyName,dwbh,Mid
	,ISNULL((Select Sum(OAA) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OAA--直接成本
	,ISNULL((Select Sum(OAB) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OAB--间接成本

	,ISNULL((Select Sum(OCA) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OCA--投资
	,ISNULL((Select Sum(OCB) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OCB--借款
	,ISNULL((Select Sum(OCC) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OCC--往来借款

	 '
	  SET @strSql3='	                                                                                                                                                                -- 
	,ISNULL((Select Sum(ODA) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As ODA--有形资产
	,ISNULL((Select Sum(ODB) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As ODB--无形资产
	,ISNULL((Select Sum(IAA) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As IAA--新增毛利
	,ISNULL((Select Sum(IAB) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As IAB--续费毛利
	,ISNULL((Select Sum(IAC) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As IAC--新增应收
	,ISNULL((Select Sum(IAD) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As IAD--新增应付
	,ISNULL((Select Sum(IAE) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As IAE--成单金额
	,ISNULL((Select Sum(IAF) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As IAF--尾款收入
	,ISNULL((Select Sum(IAG) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As IAG--非营利性收入
	  '

	SET @strSql1='                                                                                                                                                                  
    ,ISNULL((Select Sum(OEA) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OEA--市场费用
	,ISNULL((Select Sum(OEB) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OEB--日常生活
	,ISNULL((Select Sum(OEC) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OEC--行政办公
	,ISNULL((Select Sum(OED) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OED--财务费用
	,ISNULL((Select Sum(OEE) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OEE--人力费用
	,ISNULL((Select Sum(OEF) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OEF--部门基金
	,ISNULL((Select Sum(OEG) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OEG--总办费用+车辆费
	,ISNULL((Select Sum(OEH) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OEH--其他费用
	,ISNULL((Select Sum(OEI) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OEI--投资
	,ISNULL((Select Sum(OEJ) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OEJ--借款
	,ISNULL((Select Sum(OEK) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OEK--往来借款
	,ISNULL((Select Sum(OEL) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OEL--投资
	,ISNULL((Select Sum(OEM) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OEM--借款
	,ISNULL((Select Sum(OEN) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OEN--往来借款
	,ISNULL((Select Sum(OEO) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OEO--直接成本
	,ISNULL((Select Sum(OEP) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OEP--间接成本
	,ISNULL((Select Sum(OEQ) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OEQ--间接成本
	,ISNULL((Select Sum(OER) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OER--间接成本
	,ISNULL((Select Sum(OES) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As OES--间接成本
	  '
	SET @strSql2='
		,ISNULL((Select Sum(IBI) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As IBI--应有银行平整
	,ISNULL((Select Sum(IBIA) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As IBIA--银行平整收入
	,ISNULL((Select Sum(IBIB) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As IBIB--银行平整支出
	,ISNULL((Select (IBA+IBB)-(IBD+IBE) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate<=@eRq),0) As IBJ--差额
	,ISNULL((Select IBK From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate<=@eRq),0) As IBK--应收合计
	,ISNULL((Select IBL From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate<=@eRq),0) As IBL--应付合计
	From Company c '+@strSql2+'
	)	
	Select * From list,(SELECT SUM(IAA) AS IAA_a,SUM(IAB) AS IAB_a,SUM(IAC) AS IAC_a,SUM(IAD) AS IAD_a,SUM(IAE) AS IAE_a,
	SUM(IAF) AS IAF_a,SUM(IAG) AS IAG_a 
	
	
	 '
	 
	 SET @strSql4=',SUM(OAA) AS OAA_a,SUM(OAB) AS OAB_a ,SUM(OCA) AS OCA_a,SUM(OCB) AS OCB_a,SUM(OCC) AS OCC_a
	,SUM(ODA) AS ODA_a,SUM(ODB) AS ODB_a	
	,SUM(OEA) AS OEA_a,SUM(OEB) AS OEB_a,SUM(OEC) AS OEC_a,SUM(OED) AS OED_a,SUM(OEE) AS OEE_a,SUM(OEF) AS OEF_a
	,SUM(OEG) AS OEG_a,SUM(OEH) AS OEH_a,SUM(OEI) AS OEI_a,SUM(OEJ) AS OEJ_a,SUM(OEK) AS OEK_a,SUM(OEL) AS OEL_a
	,SUM(OEM) AS OEM_a,SUM(OEN) AS OEN_a,SUM(OEO) AS OEO_a,SUM(OEP) AS OEP_a,SUM(OEQ) AS OEQ_a,SUM(OER) AS OER_a,SUM(OES) AS OES_a
	 FROM list) AS n ORDER BY (Select M.CompanyNumber From ManagementArea M Where M.Id=Mid) ASC,dwbh
	 
	'
	 
PRINT (@strSql+@strSql3+@strSql1+@strSql2+@strSql4)
	--PRINT (@strSql1) 
EXEC(@strSql+@strSql3+@strSql1+@strSql2+@strSql4)

	RETURN
